﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class HanhKhach_DAO
    {
        #region Kiem tra du lieu hop le va cac quy dinh
        public static bool kiemTraMaHK(string maHK)
        {
            string query = "select mahk from HanhKhach where maHK = '" + maHK + "' ";
            DataTable hk = SqlDataAccessHelper.ExecuteQuery(query);
            if (hk.Rows.Count == 0)
                return false;
            return true;
        }
        #endregion

        #region Truy van du lieu
        public static List<HanhKhach_DTO> layDanhSachHanhKhach()
        {
            string query = "select * from HANHKHACH";
            DataTable hk_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (hk_tab == null)
                return null;
            List<HanhKhach_DTO> hk_list = new List<HanhKhach_DTO>();

            for (int i = 0; i < hk_tab.Rows.Count; i++)
            {
                hk_list.Add(new HanhKhach_DTO());
                hk_list[i].MaHK = hk_tab.Select()[i]["MaHK"].ToString();
                hk_list[i].HoTen = hk_tab.Select()[i]["Ten"].ToString();
                hk_list[i].GioiTinh = bool.Parse(hk_tab.Select()[i]["GioiTinh"].ToString());
                hk_list[i].Passport = hk_tab.Select()[i]["Passport"].ToString();
                hk_list[i].QuocTich = hk_tab.Select()[i]["QuocTich"].ToString();
                hk_list[i].CMND = hk_tab.Select()[i]["CMND"].ToString();
                hk_list[i].DiaChi = hk_tab.Select()[i]["DiaChi"].ToString();
                hk_list[i].DienThoai = hk_tab.Select()[i]["DienThoai"].ToString();

            }
            return hk_list;
        }
        public static HanhKhach_DTO layHanhKhachTheoMaHK(string maHK)
        {
            string query = "select * from hanhkhach where mahk = '" + maHK + "'";
            DataTable hk_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (hk_tab == null)
                return null;
            HanhKhach_DTO hk = new HanhKhach_DTO();
            hk.MaHK = hk_tab.Select()[0]["MaHK"].ToString();
            hk.HoTen = hk_tab.Select()[0]["Ten"].ToString();
            hk.GioiTinh = bool.Parse(hk_tab.Select()[0]["GioiTinh"].ToString());
            hk.Passport = hk_tab.Select()[0]["Passport"].ToString();
            hk.QuocTich = hk_tab.Select()[0]["QuocTich"].ToString();
            hk.CMND = hk_tab.Select()[0]["CMND"].ToString();
            hk.DiaChi = hk_tab.Select()[0]["DiaChi"].ToString();
            hk.DienThoai = hk_tab.Select()[0]["DienThoai"].ToString();
            return hk;

        }
        public static List<HanhKhach_DTO> layHanhKhachTheoHoTen(string hoTen)
        {
            string query = "select * from hanhkhach where ten like N'%" + hoTen + "%'";
            DataTable hk_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (hk_tab == null)
                return null;
            List<HanhKhach_DTO> hk_list = new List<HanhKhach_DTO>();
            for (int i = 0; i < hk_tab.Rows.Count; i++)
            {
                hk_list.Add(new HanhKhach_DTO());
                hk_list[i].MaHK = hk_tab.Select()[i]["MaHK"].ToString();
                hk_list[i].HoTen = hk_tab.Select()[i]["Ten"].ToString();
                hk_list[i].GioiTinh = bool.Parse(hk_tab.Select()[i]["GioiTinh"].ToString());
                hk_list[i].Passport = hk_tab.Select()[i]["Passport"].ToString();
                hk_list[i].QuocTich = hk_tab.Select()[i]["QuocTich"].ToString();
                hk_list[i].CMND = hk_tab.Select()[i]["CMND"].ToString();
                hk_list[i].DiaChi = hk_tab.Select()[i]["DiaChi"].ToString();
                hk_list[i].DienThoai = hk_tab.Select()[i]["DienThoai"].ToString();

            }
            return hk_list;
        }
        public static HanhKhach_DTO layHanhKhachTheoCMND(string cMND)
        {
            string query = "select * from hanhkhach where cmnd = '" + cMND + "'";
            DataTable hk_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (hk_tab == null)
                return null;
            HanhKhach_DTO hk = new HanhKhach_DTO();
            hk.MaHK = hk_tab.Select()[0]["MaHK"].ToString();
            hk.HoTen = hk_tab.Select()[0]["Ten"].ToString();
            hk.GioiTinh = bool.Parse(hk_tab.Select()[0]["GioiTinh"].ToString());
            hk.Passport = hk_tab.Select()[0]["Passport"].ToString();
            hk.QuocTich = hk_tab.Select()[0]["QuocTich"].ToString();
            hk.CMND = hk_tab.Select()[0]["CMND"].ToString();
            hk.DiaChi = hk_tab.Select()[0]["DiaChi"].ToString();
            hk.DienThoai = hk_tab.Select()[0]["DienThoai"].ToString();
            return hk;
        }
        #endregion

        #region Thao tac insert, update, delete
        //Các hàm thao tác với cơ sở dữ liệu
        public static bool themHanhKhach(HanhKhach_DTO hanhKhach)
        {
            string query = "insert into HanhKhach(mahk, ten, gioitinh, quoctich, passport, cmnd, dienthoai, diachi) "
                             + "values ('" + hanhKhach.MaHK + "', N'" + hanhKhach.HoTen + "', '" + hanhKhach.GioiTinh.ToString() + "', N'" + hanhKhach.QuocTich + "', '"
                             + hanhKhach.Passport + "', '" + hanhKhach.CMND + "', '" + hanhKhach.DienThoai + "', N'" + hanhKhach.DiaChi + "') ";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool xoaHanhKhach(HanhKhach_DTO hanhKhach)
        {
            string query = "Delete HanhKhach where maHK = '" + hanhKhach.MaHK + "' ";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool suaHanhKhach(HanhKhach_DTO hanhKhach)
        {
            string query = "Update HanhKhach Set " + ("maHK = '" + hanhKhach.MaHK + "',") + ("ten = N'" + hanhKhach.HoTen + "',") + ("gioitinh = '" + hanhKhach.GioiTinh.ToString() + "' ,")
                           + ("quoctich = N'" + hanhKhach.QuocTich + "', ") + ("passport = '" + hanhKhach.Passport + "',") + ("cmnd = '" + hanhKhach.CMND + "',")
                           + ("diachi = N'" + hanhKhach.DiaChi + "', ") + ("dienthoai = '" + hanhKhach.DienThoai + "' ") + " where maHK = '" + hanhKhach.MaHK + "' ";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        #endregion
    }
}
